package com.company;

import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.Cursor;
import com.healthmarketscience.jackcess.util.ImportUtil;
import com.sun.deploy.panel.ITreeNode;

import java.awt.*;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Map;

/**
 * Created by liupeng on 2018/10/23.
 */
public class AccessUtils {
//    /**
//     * @param args
//     * @throws IOException
//     */
//    public static void main(String[] args) throws IOException {
//        // getAccessDataTable();
//        //createTable();
//        //createTableRecByCsv() ;
//        deleteRec();
//    }
    /**
     * 读取指定表格
     * @throws IOException
     */
    public static void getAccessDataTable() throws IOException{
        Table table = DatabaseBuilder.open(new File("c:\\data.mdb")).getTable("AspCms_Collect");
        for(Row row : table) {
            System.out.println("--ID--" + row.get("CollectID")+"--Name--" + row.get("CollectName"));
        }
    }
    /**
     * 创建表并写入数据
     * @throws IOException
     */
    public static void createTable(String strPath) throws IOException{
        Database db = DatabaseBuilder.create(Database.FileFormat.V2010, new File(strPath));
        Table newTable;
        try {
            newTable = new TableBuilder("NewTable")
                    .addColumn(new ColumnBuilder("a")
                            .setSQLType(Types.INTEGER))
                    .addColumn(new ColumnBuilder("b")
                            .setSQLType(Types.VARCHAR))
                    .toTable(db);
            newTable.addRow(1, "foo");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 创建表并写入数据
     * @throws IOException
     */
    public static void createAccessTable(String strPath) throws IOException{
        Database db = DatabaseBuilder.create(Database.FileFormat.V2010, new File(strPath));
        Table newTable;
        try {
            newTable = new TableBuilder("NewTable")
                    .addColumn(new ColumnBuilder("Fileld1")
                            .setSQLType(Types.INTEGER))
                    .addColumn(new ColumnBuilder("Fileld2")
                            .setSQLType(Types.VARCHAR))
                    .toTable(db);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     *  将CSV文件的内容复制到一个新表：
     * @throws IOException
     */
    public  static void createTableRecByCsv() throws IOException{
        Database db = DatabaseBuilder.open(new File("c:\\new.mdb"));
        new ImportUtil.Builder(db, "NewTable22").setDelimiter(",").importFile(new File("c:\\test.csv"));
        db.close();
    }
    /**
     * 删除一条数据
     * @throws IOException
     */
    public static void deleteRec() throws IOException{
        Table table = DatabaseBuilder.open(new File("c:\\new.mdb")).getTable("NewTable22");
        Row row = CursorBuilder.findRow(table, Collections.singletonMap("xh", "4"));
        if(row != null) {
            System.out.println("Found row where 'a' == 'foo': " + row);
            table.deleteRow(row);
        } else {
            System.out.println("Could not find row where 'a' == 'foo'");
        }
    }

//////////////////////////////////////////////////////////////////////////
    //创建数据库
    private static Database dataBase = null;
    private static Table table = null;

    public static void CreateNewDataBase(String strPath) throws IOException {
        dataBase = DatabaseBuilder.create(Database.FileFormat.V2010, new File(strPath));
    }

    public static Table GetTable()
    {
        return table;
    }

    //创建表
    public static void CreateTable() throws IOException {
        //Table newTable;
        try {
            //应该读取xml的内容 动态配置
            TableBuilder newTable = new TableBuilder("NewTable");
            newTable = newTable.addColumn(new ColumnBuilder("Fileld1")
                            .setSQLType(Types.INTEGER));
            table = newTable.toTable(dataBase);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //添加每行数据 添加多个数据列
    public static void AddRow(Object... objectParam) throws IOException {
        table.addRow(objectParam);
    }

    /*
         打开数据库
     */
    public static void OpenDataBase(String strPath) throws IOException{
        dataBase = DatabaseBuilder.open(new File(strPath));
    }

    /*
         打开指定表
     */
    public static void OpenTable(String strTableName) throws IOException{
        table = dataBase.getTable(strTableName);
    }

    //获取但张表内的所有数据
    public static void GetEveryDataInfo()
    {
        int nColumnCount = table.getColumnCount();
        for(Row row : table)
        {
            for(int nColumn = 0; nColumn<nColumnCount; nColumn++)
            {
                String strColumnName = table.getColumns().get(nColumn).getName().toString();
                System.out.println(row.get(strColumnName));
            }
        }
    }

    /**
     * 读取指定表格数据
     */
    public static void GetTableData() throws IOException{
        Table table = DatabaseBuilder.open(new File("c:\\data.mdb")).getTable("AspCms_Collect");
        for(Row row : table) {
            System.out.println("--ID--" + row.get("CollectID")+"--Name--" + row.get("CollectName"));
        }
    }

    /*
        删除数据指定的数据
     */
    public static void DeleteData(String strColumn, Object objectParam) throws IOException{
        try {
            Row row = CursorBuilder.findRow(table, Collections.singletonMap(strColumn, objectParam));
            if(row != null) {
                table.deleteRow(row);
                dataBase.flush();
            } else {

            }
        } catch (Exception e){
            String strException = e.getMessage();
        }
    }

    //根据索引删除数据
    public static void DeleteDataFormIndex() throws IOException {
        IndexCursor cursor = new CursorBuilder(table).setIndexByColumnNames("Code").toIndexCursor();
        if(cursor.findFirstRowByEntry(1)) {
            cursor.deleteCurrentRow();
        }
    }

    //正常删除数据的方法
    public static void DeleteDataFormNorm(String strColumnName,Object objectParam ) throws IOException {
        Cursor cursor = new CursorBuilder(table).toCursor();
        Column idCol = table.getColumn(strColumnName);
        if(cursor.findFirstRow(idCol, objectParam)) {
            cursor.deleteCurrentRow();
        }
    }

    public static void CreateDataFormAuto(){
        try {
            Table newTable = new TableBuilder("diagnosis")
                    .addColumn(new ColumnBuilder("id")
                    .setAutoNumber(true)
                    .setSQLType(Types.INTEGER).toColumn())
                    .addColumn(new ColumnBuilder("name")
                    .setSQLType(Types.VARCHAR).toColumn()).toTable(dataBase);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    //更新数据库的数据
    public static void UpdateDataInfo(String strColumnName,Object OldObjectParam,Object NewObjectParam) throws IOException {
        Cursor cursor = new CursorBuilder(table).toCursor();
        Column idCol = table.getColumn(strColumnName);
        if(cursor.findFirstRow(idCol, OldObjectParam)) {
            cursor.updateCurrentRow(NewObjectParam);
        }
    }

    /*
        关闭数据库
     */
    public static void Close() throws IOException {
        try {
            dataBase.close();
        }
        catch (Exception e) {
            String strValue = e.getMessage();
        }
    }
}

